package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.xmy.cultivate.entity.Grade;
import com.xmy.cultivate.entity.RecordCourse;
import com.xmy.cultivate.entity.StudentGrade;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.views.GradeStudent;
import com.xmy.cultivate.entity.views.RenewClassRecord;
import com.xmy.cultivate.entity.views.ResStudentGrade;
import com.xmy.cultivate.entity.views.ShiftList;
import com.xmy.cultivate.respons.ResStudentGradeData;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 学生所在班级与课程 Mapper 接口
 * </p>
 *
 * @author min
 * @since 2022-08-09
 */
@Mapper
public interface StudentGradeMapper extends BaseMapper<StudentGrade> {


    /**
     * 获取最后一次分班数据
     *
     * @return
     */
    @Select("select * from student_grade where school_id =#{schoolId} and subjects_id = #{subjectsId} and student_id = #{studentId} and year_part=#{yearPart} and quarter_num=#{quarterNum} and deleted=0 and status=1 order by created_at desc limit 0,1 ")
    public abstract StudentGrade getLastForStudent(@Param("schoolId") Long schoolId, @Param("studentId") Long studentId, @Param("subjectsId") Long subjectsId, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum);


    @Select("select * from student_grade where student_id = #{studentId} and grade_id=#{gradeId} and deleted=0 and status=1 order by created_at desc limit 0,1 ")
    public abstract StudentGrade getStudentGradeForGradeId(@Param("studentId") Long studentId, @Param("gradeId") Long gradeId);


    /**
     * 获取最后一次分班数据
     *
     * @return
     */
    @Select("select * from student_grade where order_detail_id = #{orderDetailId} and deleted=0 and reading_status<>3 and status=1 order by created_at desc limit 0,1 ")
    public abstract StudentGrade getLastForOrderDetailId(@Param("orderDetailId") Long orderDetailId);


    @Select("select a.id,a.reading_status,getCourseNameForId(b.course_id) as courseName,getSchoolName(a.school_id) as schoolName,b.name as gradeName,getStaffNameForId(b.teacher_id) as teacherName,c.`name` as studentName,c.linkman,c.linkman_phone from student_grade as a LEFT JOIN grade as b on a.grade_id = b.id left JOIN student c on a.student_id = c.id ${ew.customSqlSegment}")
    public abstract IPage<ShiftList> findAll(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select count(id) as count from student_grade where student_id = #{studentId} and grade_id = #{gradeId} and status = 1 and reading_status in(1,4) and deleted=0")
    public abstract Integer studentIsAtGrade(@Param("studentId") Long studentId, @Param("gradeId") Long gradeId);

    @Select("select count(DISTINCT student_id) as count from student_grade where school_id = #{schoolId} and grade_id = #{gradeId} and status = 1 and reading_status = 1 and deleted=0")
    public abstract Integer studentCount(@Param("schoolId") Long schoolId, @Param("gradeId") Long gradeId);

    /**
     * 预分班人数
     * @param schoolId
     * @param gradeId
     * @return
     */
    @Select("select count(DISTINCT student_id) as count from student_grade where school_id = #{schoolId} and grade_id = #{gradeId} and status = 1 and reading_status = 0 and deleted=0")
    public abstract Integer studentAdvanceCount(@Param("schoolId") Long schoolId, @Param("gradeId") Long gradeId);

    @Select("select c.`name` from student_grade as a LEFT JOIN grade as b on a.grade_id = b.id LEFT JOIN staff as c on b.teacher_id = c.id where a.student_id =#{studentId} and a.subjects_id=#{subjectsId} and a.year_part = #{yearPart}  and a.quarter_num = #{quarterNum} ORDER BY b.created_at DESC limit 1")
    public abstract List<String> getGradeTeacherNameList(@Param("schoolId") Long schoolId, @Param("studentId") Long studentId, @Param("subjectsId") Long subjectsId, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum);

    /**
     * 获取班级使用课时
     *
     * @param gradeId
     * @param studentId
     * @return
     */
    @Select("select IFNULL(sum(use_course_count),0) as use_course_count from student_grade where grade_id = #{gradeId} and student_id=#{studentId} and deleted = 0  and status<>3")
    public abstract Integer useCourseCount(@Param("gradeId") Long gradeId, @Param("studentId") Long studentId);

    /**
     * 获取所学生在校区所在班级使用课时
     *
     * @return
     */
    //Select("select sum(use_course_count) as use_course_count from student_grade ${ew.customSqlSegment}")
    @Select("select ifnull(sum(use_course_count),0) as use_course_count from student_grade where deleted = 0  and status<>3 and school_id=#{schoolId} and student_id=#{studentId} and subjects_id = #{subjectsId}")
    public abstract Integer getStudentGradeUseCount(@Param("schoolId") Long schoolId, @Param("studentId") Long studentId, @Param("subjectsId") Long subjectsId);

    @Select("select * from (select a.id,a.student_id,a.grade_id,a.use_course_count,(select sum(buckle_class_house) from record_course as b where b.grade_id=a.grade_id and b.student_id =a.student_id) as use_count from student_grade as a) as t  where use_course_count<>use_count")
    public abstract List<Map<String, Object>> getTest();

    @Select("select * from student_grade ${ew.customSqlSegment}")
    public abstract List<StudentGrade> getStudentGradeList(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

    /**
     * 获取在读学生数
     *
     * @param queryWrapper
     * @return
     */
    @Select("select count(count) as count from (select count(DISTINCT student_id) as count from student_grade  ${ew.customSqlSegment}) as t")
    public abstract Long getStudentCount(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);


    /**
     * 分组获取最新一条数据
     *
     * @param queryWrapper
     * @return
     */
    //@Select("select t1.* from student_grade as t1,(select max(id) as id from student_grade where year_part = 2023 and quarter_num = 2 GROUP BY student_id,subjects_id)  as t2 where t1.id = t2.id")
    @Select("select t1.* from student_grade as t1,(select max(id) as id from student_grade ${ew.customSqlSegment})  as t2 where t1.id = t2.id")
    public abstract List<StudentGrade> getGroupByAtNewList(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

    /**
     * 分组获取最新一条数据
     *
     * @param queryWrapper
     * @return
     */
    //@Select("select t1.* from student_grade as t1,(select max(id) as id from student_grade where year_part = 2023 and quarter_num = 2 GROUP BY student_id,subjects_id)  as t2 where t1.id = t2.id")
    @Select("select t1.student_id from student_grade as t1,(select max(id) as id from student_grade_view ${ew.customSqlSegment})  as t2 where t1.id = t2.id")
    public abstract List<Long> getGroupByAtNewStudentIdList(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

    /**
     * 在读学员
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @return
     */
    @Select({"<script>",
            "select count(DISTINCT t4.id) as count from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,use_course_count,residue_course_count,IFNULL(stop_status_count,0) as stop_status_count,IFNULL(reading_status_count,0) as reading_status_count,is_out,is_turn_out",
            "from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,SUM(use_course_count) as use_course_count,SUM(residue_course_count) as residue_course_count,SUM(CASE WHEN t1.out_house&gt;0 THEN 1 ELSE 0 END) as is_out,SUM(CASE WHEN t1.turn_out_house&gt;0 THEN 1 ELSE 0 END) as is_turn_out,",
            "(select SUM(case reading_status WHEN 4 THEN 1 ELSE 0 END) as stop_status_count from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t1.lesson_type = t2.lesson_type and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as stop_status_count,",
            "(select SUM(case reading_status WHEN 1 THEN 1 ELSE 0 END) as reading_status_count from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t1.lesson_type = t2.lesson_type and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as reading_status_count",
            "from order_detail as t1",
            "where year_part = ${yearPart} and quarter_num = ${quarterNum} and `status`&lt;&gt;2 and deleted = 0",
            "<when test='lessonType!=null and lessonType!=&apos;&apos;'>",
            "and lesson_type = ${lessonType}",
            "</when>",
            "GROUP BY year_part,quarter_num,school_id,subjects_id,student_id",
            ") as t3 GROUP BY school_id,subjects_id,student_id",
            ") as t4",
            "LEFT JOIN student_grade AS t5 ON t4.student_id = t5.student_id ",
            "AND t4.year_part = t5.year_part ",
            "AND t4.quarter_num = t5.quarter_num ",
            "AND t4.subjects_id = t5.subjects_id ",
            "AND t4.school_id = t5.school_id ",
            "AND t5.use_course_count &gt; 0 ",
            "AND t5.`status` = 1 ",
            "AND t5.`reading_status` = 1 ",
//            "AND t4.id = t5.order_detail_id ",
            "<when test='lessonType!=null and lessonType!=&apos;&apos;'>",
            "AND t5.lesson_type = ${lessonType} ",
            "</when>",
            "AND t5.deleted = 0 ",
//            "AND t5.is_last = 1 ",
            "LEFT JOIN grade AS t6 ON t5.grade_id = t6.id AND t6.deleted = 0 ",
            "where",
            "t4.use_course_count &gt; 0 and t4.residue_course_count &gt;0 and t4.reading_status_count&gt;0 and t4.stop_status_count=0",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
                "and t4.`school_id` IN (${schoolId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                "and t4.`subjects_id` = ${subjectsId}",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
                "and t6.year_class_id IN (${yearClassId})",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
                "and t6.teacher_id = ${teacherId}",
            "</when>",
            "<when test='status!=null and status!=&apos;&apos;'>",
                "and t6.`status` = ${status}",
            "</when>",
            "<when test='isScheduling!=null and isScheduling!=&apos;&apos;'>",
                "<when test='isScheduling==0'>",
                    "AND t6.id NOT IN (select grade_id from course_scheduling_detail where deleted=0 GROUP BY grade_id)",
                "</when>",
                "<when test='isScheduling==1'>",
                    "AND t6.id IN (select grade_id from course_scheduling_detail where deleted=0 GROUP BY grade_id)",
                "</when>",
            "</when>",
            "<when test='sourseLeve == 2'>",
                "and t6.id in(select id from grade where teacher_id = ${adminId})",
            "</when>",
            "</script>"})
    public abstract Integer readingStudentCount(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("schoolId") String schoolId, @Param("subjectsId") String subjectsId, @Param("lessonType") String lessonType, @Param("yearClassId") String yearClassId, @Param("teacherId") String teacherId, @Param("status") String status,@Param("isScheduling") String isScheduling, @Param("sourseLeve") Integer sourseLeve, @Param("adminId") Long adminId);

    /**
     * 结课学员数量
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @return
     */
    @Select({"<script>",
            "select count(id) as count from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,use_course_count,residue_course_count,IFNULL(stop_status_count,0) as stop_status_count,IFNULL(reading_status_count,0) as reading_status_count,is_out,is_turn_out",
            "from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,SUM(use_course_count) as use_course_count,SUM(residue_course_count) as residue_course_count,SUM(CASE WHEN t1.out_house&gt;0 THEN 1 ELSE 0 END) as is_out,SUM(CASE WHEN t1.turn_out_house&gt;0 THEN 1 ELSE 0 END) as is_turn_out,",
            "(select SUM(case reading_status WHEN 4 THEN 1 ELSE 0 END) as stop_status_count from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t1.lesson_type = t2.lesson_type and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as stop_status_count,",
            "(select SUM(case reading_status WHEN 1 THEN 1 ELSE 0 END) as reading_status_count from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t1.lesson_type = t2.lesson_type and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as reading_status_count",
            "from order_detail as t1",
            "where year_part = ${yearPart} and quarter_num = ${quarterNum} and `status`&lt;&gt;2 and deleted = 0",
            "<when test='lessonType!=null and lessonType!=&apos;&apos;'>",
            "and lesson_type =${lessonType}",
            "</when>",
            "GROUP BY year_part,quarter_num,school_id,subjects_id,student_id",
            ") as t3 GROUP BY school_id,subjects_id,student_id",
            ") as t4 where ((residue_course_count = 0 and reading_status_count = 0 and stop_status_count=0 and is_out=0 and is_turn_out=0) or (residue_course_count > 0 and use_course_count &gt; 0 and reading_status_count = 0 and stop_status_count=0))",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and `school_id` = ${schoolId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and `subjects_id` = ${subjectsId}",
            "</when>",
            "</script>"})
    public abstract Integer countStudentByStatusJoint(@Param("yearPart") String yearPart,@Param("quarterNum") String quarterNum,@Param("schoolId") String schoolId,@Param("subjectsId") String subjectsId,@Param("lessonType") String lessonType);




    /**
     * 异常学员
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @return
     */
    @Select({"<script>",
            "select count(id) as count from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,use_course_count,residue_course_count,IFNULL(stop_status_count,0) as stop_status_count,IFNULL(reading_status_count,0) as reading_status_count,is_out,is_turn_out",
            "from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,SUM(use_course_count) as use_course_count,SUM(residue_course_count) as residue_course_count,SUM(CASE WHEN t1.out_house&gt;0 THEN 1 ELSE 0 END) as is_out,SUM(CASE WHEN t1.turn_out_house&gt;0 THEN 1 ELSE 0 END) as is_turn_out,",
            "(select SUM(case reading_status WHEN 4 THEN 1 ELSE 0 END) as stop_status_count from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t1.lesson_type = t2.lesson_type and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as stop_status_count,",
            "(select SUM(case reading_status WHEN 1 THEN 1 ELSE 0 END) as reading_status_count from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t1.lesson_type = t2.lesson_type and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as reading_status_count",
            "from order_detail as t1",
            "where year_part = ${yearPart} and quarter_num = ${quarterNum} and `status`&lt;&gt;2 and deleted = 0",
            "<when test='lessonType!=null and lessonType!=&apos;&apos;'>",
            "and lesson_type =${lessonType}",
            "</when>",
            "GROUP BY year_part,quarter_num,school_id,subjects_id,student_id",
            ") as t3 GROUP BY school_id,subjects_id,student_id",
            ") as t4 where use_course_count = 0 and residue_course_count&gt;0",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and `school_id` = ${schoolId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and `subjects_id` = ${subjectsId}",
            "</when>",
            "</script>"})
    public abstract Integer abnormalStudentCount(@Param("yearPart") String yearPart,@Param("quarterNum") String quarterNum,@Param("schoolId") String schoolId,@Param("subjectsId") String subjectsId,@Param("lessonType") String lessonType);


    /**
     * 退费学员
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @return
     */
    @Select({"<script>",
            "select count(id) as count from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,use_course_count,residue_course_count,IFNULL(stop_status_count,0) as stop_status_count,IFNULL(reading_status_count,0) as reading_status_count,is_out,is_turn_out",
            "from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,SUM(use_course_count) as use_course_count,SUM(residue_course_count) as residue_course_count,SUM(CASE WHEN t1.out_house&gt;0 THEN 1 ELSE 0 END) as is_out,SUM(CASE WHEN t1.turn_out_house&gt;0 THEN 1 ELSE 0 END) as is_turn_out,",
            "(select SUM(case reading_status WHEN 4 THEN 1 ELSE 0 END) as stop_status_count from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t1.lesson_type = t2.lesson_type and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as stop_status_count,",
            "(select SUM(case reading_status WHEN 1 THEN 1 ELSE 0 END) as reading_status_count from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t1.lesson_type = t2.lesson_type and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as reading_status_count",
            "from order_detail as t1",
            "where year_part = ${yearPart} and quarter_num = ${quarterNum} and `status`&lt;&gt;2 and deleted = 0",
            "<when test='lessonType!=null and lessonType!=&apos;&apos;'>",
            "and lesson_type =${lessonType}",
            "</when>",
            "GROUP BY year_part,quarter_num,school_id,subjects_id,student_id",
            ") as t3 GROUP BY school_id,subjects_id,student_id",
            ") as t4 where residue_course_count=0 and is_out&gt;0 and is_turn_out=0",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and `school_id` = ${schoolId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and `subjects_id` = ${subjectsId}",
            "</when>",
            "</script>"})
    public abstract Integer returnStudentCount(@Param("yearPart") String yearPart,@Param("quarterNum") String quarterNum,@Param("schoolId") String schoolId,@Param("subjectsId") String subjectsId,@Param("lessonType") String lessonType);




    /**
     * 停课学员
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @return
     */
    @Select({"<script>",
            "select count(id) as count from (",
                "select id,year_part,quarter_num,school_id,subjects_id,student_id,use_course_count,residue_course_count,IFNULL(stop_status_count,0) as stop_status_count,IFNULL(reading_status_count,0) as reading_status_count,is_out,is_turn_out",
                "from (",
                    "select id,year_part,quarter_num,school_id,subjects_id,student_id,SUM(use_course_count) as use_course_count,SUM(residue_course_count) as residue_course_count,SUM(CASE WHEN t1.out_house&gt;0 THEN 1 ELSE 0 END) as is_out,SUM(CASE WHEN t1.turn_out_house&gt;0 THEN 1 ELSE 0 END) as is_turn_out,",
                    "(select SUM(case reading_status WHEN 4 THEN 1 ELSE 0 END) as stop_status_count from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t1.lesson_type = t2.lesson_type  and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as stop_status_count,",
                    "(select SUM(case reading_status WHEN 1 THEN 1 ELSE 0 END) as reading_status_count from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t1.lesson_type = t2.lesson_type and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as reading_status_count",
                "from order_detail as t1",
                "where year_part = ${yearPart} and quarter_num = ${quarterNum} and `status`&lt;&gt;2 and deleted = 0",
                "<when test='lessonType!=null and lessonType!=&apos;&apos;'>",
                "and lesson_type =${lessonType}",
                "</when>",
                "GROUP BY year_part,quarter_num,school_id,subjects_id,student_id",
                ") as t3 GROUP BY school_id,subjects_id,student_id",
            ") as t4 where use_course_count&gt;0 and residue_course_count&gt;0 and stop_status_count&gt;0",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and `school_id` = ${schoolId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and `subjects_id` = ${subjectsId}",
            "</when>",
            "</script>"})
    public abstract Integer stopStudnetCount(@Param("yearPart") String yearPart,@Param("quarterNum") String quarterNum,@Param("schoolId") String schoolId,@Param("subjectsId") String subjectsId,@Param("lessonType") String lessonType);


    /**
     * 转校学员
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @return
     */
    @Select({"<script>",
            "select count(id) as count from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,use_course_count,residue_course_count,IFNULL(stop_status_count,0) as stop_status_count,IFNULL(reading_status_count,0) as reading_status_count,is_out,is_turn_out",
            "from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,SUM(use_course_count) as use_course_count,SUM(residue_course_count) as residue_course_count,SUM(CASE WHEN t1.out_house&gt;0 THEN 1 ELSE 0 END) as is_out,SUM(CASE WHEN t1.turn_out_house&gt;0 THEN 1 ELSE 0 END) as is_turn_out,",
            "(select SUM(case reading_status WHEN 4 THEN 1 ELSE 0 END) as stop_status_count from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t1.lesson_type = t2.lesson_type and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as stop_status_count,",
            "(select SUM(case reading_status WHEN 1 THEN 1 ELSE 0 END) as reading_status_count from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t1.lesson_type = t2.lesson_type and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as reading_status_count",
            "from order_detail as t1",
            "where year_part = ${yearPart} and quarter_num = ${quarterNum} and `status`&lt;&gt;2 and deleted = 0",
            "<when test='lessonType!=null and lessonType!=&apos;&apos;'>",
            "and lesson_type =${lessonType}",
            "</when>",
            "GROUP BY year_part,quarter_num,school_id,subjects_id,student_id",
            ") as t3 GROUP BY school_id,subjects_id,student_id",
            ") as t4 where  is_turn_out&gt;0 and residue_course_count = 0 and is_out=0",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and `school_id` = ${schoolId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and `subjects_id` = ${subjectsId}",
            "</when>",
            "</script>"})
    public abstract Integer turnOutStudentCount(@Param("yearPart") String yearPart,@Param("quarterNum") String quarterNum,@Param("schoolId") String schoolId,@Param("subjectsId") String subjectsId,@Param("lessonType") String lessonType);




    /**
     * 批量核对
     *
     * @param gradeId
     * @param studentId
     * @return
     */
    @Select("UPDATE student_grade SET is_check_house = 1 WHERE grade_id = #{gradeId} AND student_id = #{studentId} AND status <> 3 AND deleted = 0")
    public abstract Integer updateIsCheckHouse(@Param("gradeId") String gradeId, @Param("studentId") String studentId);


    @Select({"<script>",
            "select count(id) as count from (",
            "select id,student_id,school_id,subjects_id from student_grade as t1 ",
            "where 1=1",
            "<when test='sourseLeve == 2'>",
            "and t1.grade_id in(select id from grade where teacher_id = ${adminId})",
            "</when>",
            "<when test='lessonType!=null and lessonType!=&apos;&apos;'>",
            "and t1.lesson_type = ${lessonType}",
            "</when>",
            "<when test='yearPart!=null and yearPart!=&apos;&apos;'>",
            "and t1.year_part = ${yearPart}",
            "</when>",
            "<when test='quarterNum!=null and quarterNum!=&apos;&apos;'>",
            "and t1.`quarter_num` = ${quarterNum}",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and t1.`school_id` in(${schoolId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and t1.`subjects_id` = ${subjectsId}",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
            "and t1.`year_class_id` in (${yearClassId}) ",
            "</when>",
            "<when test='courseId!=null and courseId!=&apos;&apos;'>",
            "and grade_id in(select id from grade where course_id = ${courseId})",
            "</when>",
            "<when test='isCheckHouse!=null and isCheckHouse!=&apos;&apos;'>",
            "and t1.is_check_house =${isCheckHouse}",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
            "and t1.grade_id in(select id from grade where teacher_id = ${teacherId})",
            "</when>",
            "and `status`&lt;&gt;3 ",
            "and (t1.reading_status in(1,2,4,5,6) or (t1.reading_status = 3 and t1.use_course_count&gt;0))",
            "and t1.deleted=0 ",
            "GROUP BY t1.school_id,t1.student_id,t1.subjects_id",
            ") as t",
            "</script>"})
    public abstract Long getStudentCheckCount(@Param("yearPart") String yearPart,@Param("quarterNum") String quarterNum,@Param("schoolId") String schoolId,@Param("subjectsId") String subjectsId,@Param("yearClassId")String yearClassId,@Param("courseId")String courseId,@Param("teacherId") String teacherId,@Param("isCheckHouse") String isCheckHouse,@Param("lessonType") String lessonType,@Param("sourseLeve") Integer sourseLeve,@Param("adminId") Long adminId);

    /**
     * 获取是否分过班
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @param studentId
     * @return
     */
    @Select("select count(id) as count from student_grade where year_part = ${yearPart} and quarter_num = ${quarterNum} and school_id = ${schoolId} and subjects_id =${subjectsId} and student_id = ${studentId} and `status`<>3 and deleted = 0")
    public abstract Long getStudentGradeCount(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("schoolId") Long schoolId,@Param("subjectsId") Long subjectsId,@Param("studentId") Long studentId);


    /**
     * 本学期使用课时
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @param studentId
     * @return
     */
    @Select("select IFNULL(sum(use_course_count),0) as count from student_grade  where year_part = ${yearPart} and quarter_num = ${quarterNum} and school_id = ${schoolId} and subjects_id =${subjectsId} and student_id = ${studentId} and `status`<>3 and deleted = 0 and lesson_type =${lessonType}")
    public abstract Long getStudentUseCourseCount(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("schoolId") Long schoolId,@Param("subjectsId") Long subjectsId,@Param("studentId") Long studentId,@Param("lessonType") Integer lessonType);


    /**
     * 获取本学期使用课时(不分校区)
     * @param
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "IFNULL( sum( t1.use_course_count ), 0 ) AS count ",
            "FROM",
            "student_grade AS t1",
            "LEFT JOIN grade AS t2 ON t1.grade_id = t2.id ",
            "WHERE",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.subjects_id = ${subjectsId} ",
            "AND t1.student_id = ${studentId} ",
            "AND t1.lesson_type = ${lessonType} ",
            "AND t1.deleted = 0 ",
            "AND t1.STATUS &lt;&gt; 3 ",
            "AND t2.is_makeup = 0",
            "</script>"})
    public abstract Long getStudentUseCourseCountAllSchool(@Param("yearPart") Integer yearPart,
                                                  @Param("quarterNum") Integer quarterNum,
                                                  @Param("subjectsId") Long subjectsId,
                                                  @Param("studentId") Long studentId,
                                                  @Param("lessonType") Integer lessonType);


    /**
     * 是否使用满15次课，不分校区
     * @param yearPart
     * @param quarterNum
     * @param subjectsId
     * @param studentId
     * @param lessonType
     * @return
     */
    @Select("select IFNULL(sum(buckle_class_house),0) as count from record_course  where year_part = ${yearPart} and `quarter` = ${quarterNum} and subjects_id =${subjectsId} and student_id = ${studentId} and is_deduct_house =1 and buckle_class_house>0 and deleted = 0 and lesson_type =${lessonType}")
    public abstract Long getStudentUseCourseCountForRecourse(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("subjectsId") Long subjectsId,@Param("studentId") Long studentId,@Param("lessonType") Integer lessonType);



    @Select({"<script>",
            "SELECT",
                    "id,getSchoolName(school_id) as school_name,grade_name,student_name,subjects_name,lesson_type,week_dates,time_start,time_end,getDictName(lesson_type, 23) as lesson_name",
                    "FROM",
                    "student_grade_view ",
                    "WHERE",
                    "year_part = ${yearPart} ",
                    "AND quarter_num = ${quarterNum} ",
                    "AND week_dates = ${weekDates} ",
                    "and reading_status = 1",
                    "AND (",
                    "( time_start &gt; #{timeStart} AND time_start &lt; #{timeEnd} ) ",
                    "OR ( time_end &gt; #{timeStart} AND time_end &lt; #{timeEnd} ) ",
                    "  OR ( time_start &lt; #{timeStart} AND time_end &gt; #{timeEnd} )",
                    ") ",
                    "AND (student_id in(${studentStr}) or student_id IN ( SELECT entity_id FROM student_face_data WHERE student_id IN (${studentStr}) AND deleted = 0 )) ",
                    "<when test='lessonType!=null and lessonType!=&apos;&apos; '>",
                    "  and lesson_type = #{lessonType}",
                    "</when>",
                    "GROUP BY",
                    "school_id,",
                    "subjects_id,",
                    "lesson_type,",
                    "student_id",
            "</script>"})
    public abstract List<ResStudentGradeData> getStudentScheduling(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("weekDates") String weekDates,@Param("timeStart") String timeStart,@Param("timeEnd") String timeEnd,@Param("studentStr") String studentStr,@Param("lessonType") String lessonType);

    @Select("SELECT grade.*,grade.year_class_id as yearClassInt FROM grade LEFT JOIN student_grade ON student_grade.grade_id = grade.id  ${ew.customSqlSegment}")
    public abstract List<Grade> getGrade(@Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 按班级复课
     * @param yearPart
     * @param quarterNum
     * @param gradeId
     * @return
     */
    @Update("update student_grade set reading_status =1 where grade_id = ${gradeId}  and year_part = ${yearPart} and quarter_num = ${quarterNum} and `status`<>3 and reading_status = 4 and deleted = 0")
    public abstract Integer agrainForGrade(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("gradeId") Long gradeId);


    /**
     * 获取精品课班型
     * @param yearPart
     * @param quarterNum
     * @param studentId
     * @param subjectsId
     * @return
     */
    @Select("select * from (select t1.school_id,t1.student_id,t1.subjects_id,t1.lesson_type,t1.year_part,t1.quarter_num,t2.course_type_id,t3.name as course_type_name,t3.name as class_type_name,t2.teacher_id,t4.name as teacher_name,t2.name,t2.year_class_id  from student_grade as t1 LEFT JOIN grade as t2 on t1.grade_id = t2.id LEFT JOIN course_type as t3 on t2.course_type_id = t3.id LEFT JOIN staff as t4 on t2.teacher_id = t4.id where t1.deleted = 0 and t1.is_last=1 and t1.lesson_type =1 and t1.year_part = ${yearPart} and t1.quarter_num = ${quarterNum} and t1.subjects_id = ${subjectsId} and t1.student_id = ${studentId}) as t")
    public abstract Grade getClassTypeNameForStudentId(@Param("yearPart") Integer yearPart,
                                                        @Param("quarterNum") Integer quarterNum,
                                                        @Param("studentId") Long studentId,
                                                        @Param("subjectsId") Long subjectsId);

    /**
     * 获取班型
     * @param yearPart
     * @param quarterNum
     * @param subjectsId
     * @param studentId
     * @param lessonType
     * @return
     */
    @Select({"<script>",
            "SELECT",
                    "t1.school_id,",
                    "t1.student_id,",
                    "t1.subjects_id,",
                    "t1.year_class_id,",
                    "t1.lesson_type,",
                    "t1.year_part,",
                    "t1.quarter_num,",
                    "t2.course_type_id as class_type_id,",
                    "t3.NAME AS class_type_name,",
                    "t4.NAME AS teacher_name ",
                    "FROM",
                    "student_grade AS t1",
                    "LEFT JOIN grade AS t2 ON t1.grade_id = t2.id",
                    "LEFT JOIN course_type AS t3 ON t2.course_type_id = t3.id",
                    "LEFT JOIN staff AS t4 ON t2.teacher_id = t4.id ",
                    "WHERE",
                    "t1.deleted = 0 ",
                    "AND t1.is_last = 1 ",
                    "AND t1.lesson_type = ${lessonType} ",
                    "AND t1.year_part = ${yearPart} ",
                    "AND t1.quarter_num = ${quarterNum} ",
                    "AND t1.subjects_id = ${subjectsId} ",
                    "AND t1.reading_status = 1 ",
                    "AND t1.student_id = ${studentId} ",
                    "ORDER BY",
                    "t1.id DESC ",
                    "LIMIT 1",
            "</script>"})
    public abstract ResStudentGrade getStudentGradeClassType(@Param("yearPart") Integer yearPart,
                                                             @Param("quarterNum") Integer quarterNum,
                                                             @Param("subjectsId") Long subjectsId,
                                                             @Param("studentId") Long studentId,
                                                             @Param("lessonType") Integer lessonType);



    @Select("select t1.* from student_grade as t1,(select max(id) as id from student_grade_view ${ew.customSqlSegment})  as t2 where t1.id = t2.id")
    public abstract List<StudentGrade> getGroupStudentList(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);


    /**
     * 获取最后一条数据，不区分is_last
     * @param yearPart
     * @param quarterNum
     * @param subjectsId
     * @param studentId
     * @param lessonType
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.year_class_id,",
            "t1.lesson_type,",
            "t1.year_part,",
            "t1.quarter_num,",
            "t1.grade_id",
            "FROM",
            "student_grade AS t1",
            "WHERE",
            "t1.deleted = 0 ",
            "AND t1.lesson_type = ${lessonType}",
            "AND t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.subjects_id = ${subjectsId} ",
            "AND t1.student_id = ${studentId} ",
            "and t1.status &lt;&gt; 3",
            "ORDER BY",
            "t1.id DESC ",
            "LIMIT 1",
            "</script>"})
    public abstract StudentGrade getLastOne(@Param("yearPart") Integer yearPart,
                                            @Param("quarterNum") Integer quarterNum,
                                            @Param("subjectsId") Long subjectsId,
                                            @Param("studentId") Long studentId,
                                            @Param("lessonType") Integer lessonType);


    /**
     * 获取最后一次分班在读信息
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @param studentId
     * @return
     */
    @Select({"<script>",
            "select *,getStaffNameForId(teacher_id) as teacher_name from student_grade_view where year_part = ${yearPart} and quarter_num = ${quarterNum} and school_id = ${schoolId} and student_id = ${studentId} and lesson_type =1 and reading_status =1 and deleted =0",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                "AND subjects_id = ${subjectsId} ",
            "</when>",
            " ORDER BY id desc limit 1",
            "</script>"})
    public RecordCourse getLastReadingGrade(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("schoolId") Long schoolId, @Param("subjectsId") Long subjectsId, @Param("studentId") Long studentId);


    @Select("select t1.* from student_grade as t1 INNER JOIN (select MAX(id) as id from student_grade where deleted =0 and `status`<>3 and year_part = ${yearPart} and quarter_num = ${quarterNum} and lesson_type = ${lessonType} and student_id =${studentId} and subjects_id = ${subjectsId}  GROUP BY student_id,subjects_id) as t2 on t1.id = t2.id limit 1")
    public StudentGrade getLastForMaxId(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("subjectsId") Long subjectsId, @Param("studentId") Long studentId, @Param("lessonType") Integer lessonType);


    @Select({"<script>",
            "SELECT",
            "t1.* ",
            "FROM",
            "student_grade AS t1",
            "INNER JOIN (",
            "SELECT",
            "MAX( id ) AS id ",
            "FROM",
            "student_grade ",
            "WHERE",
            "year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND lesson_type = 1 ",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "AND school_id = ${schoolId} ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "AND subjects_id = ${subjectsId} ",
            "</when>",
            "<when test='studentId!=null and studentId!=&apos;&apos;'>",
            "AND student_id = ${studentId} ",
            "</when>",
            "AND deleted = 0 ",
            "AND `status` &lt;&gt; 3 ",
            "GROUP BY",
            "student_id,",
            "subjects_id ",
            ") AS t2 ON t1.id = t2.id",
            "</script>"})
    public IPage<StudentGrade> getStudentGradeLastList(IPage<?> page,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("schoolId") Long schoolId,@Param("subjectsId") Long subjectsId,@Param("studentId") Long studentId);

    @Select("select count(id) from student_grade where year_part = ${yearPart} and quarter_num = ${quarterNum} and subjects_id = ${subjectsId} and student_id = ${studentId} and lesson_type = ${lessonType} and is_presort in(${presortStr}) and deleted =0 and `status`<>3")
    public Integer getPresortCount(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("subjectsId") Long subjectsId, @Param("studentId") Long studentId, @Param("lessonType") Integer lessonType,@Param("presortStr") String presortStr);

    /**
     * 调整分班学员班型
     *
     * @param classTypeId
     * @param gradeId
     * @return
     */
    @Select("UPDATE student_grade SET class_type_id_new = #{classTypeId} WHERE grade_id = #{gradeId} ")
    public Boolean updateClassTypeByGradeId(@Param("classTypeId") Long classTypeId, @Param("gradeId") Long gradeId);


    @Select("select * from student_grade where student_id = ${studentId} and grade_id = ${gradeId} and deleted = 0 and `status`<>2 and is_presort in(1,2) order by  id desc limit 1")
    public StudentGrade getPresortData(@Param("studentId") Long studentId,@Param("gradeId") Long gradeId);


    /**
     * 获取老师带学员使用的课次
     */
    @Select({"<script>",
            "SELECT SUM( t1.use_course_count ) AS use_course_count ",
            "FROM ",
            "student_grade AS t1 ",
            "INNER JOIN grade AS t2 ON t1.grade_id = t2.id ",
            "WHERE ",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.lesson_type = 1 ",
            "AND t1.subjects_id = ${subjectsId} ",
            "AND t1.student_id = ${studentId} ",
            "AND t2.teacher_id = ${teacherId} ",
            "AND t1.deleted = 0 ",
            "AND t1.`status` &lt;&gt; 2 ",
            "GROUP BY t2.teacher_id,t1.student_id ",
            "</script>"})
    public abstract Integer useCourseCountByTeacherId(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("subjectsId") Long subjectsId, @Param("studentId") Long studentId, @Param("teacherId") Long teacherId);


    @Select({"<script>",
            "SELECT",
                    "GROUP_CONCAT(t2.name) as grade_name",
                    "FROM",
                    "student_grade AS t1",
                    "INNER JOIN grade AS t2 ON t1.grade_id = t2.id ",
                    "WHERE",
                    "t1.year_part = ${yearPart} ",
                    "AND t1.quarter_num = ${quarterNum} ",
                    "AND t1.student_id = ${studentId} ",
                    "AND t2.week_dates = ${weekDates} ",
                    "AND t1.deleted = 0 ",
                    "AND t1.reading_status IN (0,1) GROUP BY t2.id",
            "</script>"})
    public abstract String getWeekDatesPlanGrade(@Param("yearPart") Integer yearPart,
                                                 @Param("quarterNum") Integer quarterNum,
                                                 @Param("studentId") Long studentId,
                                                 @Param("weekDates") String weekDates
    );

    @Select({"<script>",
            "SELECT",
            "GROUP_CONCAT(t2.name) as grade_name",
            "FROM",
            "student_grade AS t1",
            "INNER JOIN grade AS t2 ON t1.grade_id = t2.id ",
            "WHERE",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.student_id = ${studentId} ",
            "AND t2.week_dates = ${weekDates} ",
            "AND t2.lesson_type = ${lessonType} ",
            "AND t1.deleted = 0 ",
            "AND t1.reading_status IN (0,1) GROUP BY t2.id",
            "</script>"})
    public abstract String getWeekDatesPlanGradeByLessonType(@Param("yearPart") Integer yearPart,
                                                             @Param("quarterNum") Integer quarterNum,
                                                             @Param("studentId") Long studentId,
                                                             @Param("weekDates") String weekDates,
                                                             @Param("lessonType") Integer lessonType
    );
}
